﻿using HARTCalibrationTool.SerialPortManager;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HARTCalibrationTool
{
    public class ExcelHelper
    {
        public int[] _currentDataCnt = new int[32];
        public static int currentChl = 0xFE;

        private const int _Channels = 32;
        private const int _Parameters = 3;

        /*
         * 例如：变送器1 复测数据
         * {
                new List<string> {"1", "2024-12-13 14:34:50", "0.2000", "25℃"};
                new List<string> {"2", "2024-12-13 14:34:59", "0.3000", "25℃"};
                new List<string> {"3", "2024-12-13 14:35:08", "0.4000", "25℃"};
                new List<string> {"4", "2024-12-13 14:35:17", "0.5000", "25℃"};
           }
         */
        public List<List<string>>[] retestData = new List<List<string>>[32];
        public bool[,] collectionStatus = new bool[_Channels, _Parameters];

        public List<string>[] upRetestPVData = new List<string>[32];
        public List<string>[] downRetestPVData = new List<string>[32];

        public List<string>[] upRetestCurrentData = new List<string>[32];
        public List<string>[] downRetestCurrentData = new List<string>[32];

        public ExcelHelper() 
        {
            for (int i = 0; i < 32; i++)
            {
                _currentDataCnt[0] = 0;
                retestData[i] = new List<List<string>>();

                upRetestPVData[i] = new List<string>();
                downRetestPVData[i] = new List<string>();
                upRetestCurrentData[i] = new List<string>();
                downRetestCurrentData[i] = new List<string>();
            }
        }

        public void ClrExcelDataBuffer()
        {
            for (int i = 0; i < 32; i++)
            {
                upRetestPVData[i].Clear();
                downRetestPVData[i].Clear();
                upRetestCurrentData[i].Clear();
                downRetestCurrentData[i].Clear();
            }
        }

        public bool CreateExcelFile(string filePath)
        {
            FileInfo fileInfo = new FileInfo(filePath);
            bool fileExists = fileInfo.Exists;

            bool result = false;

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            try
            {
                if (!File.Exists(filePath))
                {
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        ExcelWorksheet worksheet;

                        worksheet = package.Workbook.Worksheets.Add("数据");

                        package.SaveAs(new FileInfo(filePath));
                    }
                }
                else
                {
                    SerialPortsBase.PrintMessage($"文件{filePath}已存在！");
                }
            }
            catch (Exception ex)
            {
                SerialPortsBase.PrintMessage($"Excel文件（{filePath}）创建失败！");
            }

            return result;
        }

        public List<string> ReadColumnsUntilEmpty(string filePath, string sheetName, int startRow, int startCol)
        {
            FileInfo file = new FileInfo(filePath);
            var columnData = new List<string>();

            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];

                if (startRow <= 0 || startCol <= 0)
                {
                    throw new ArgumentException("起始行和列必须大于 0");
                }

                int rowIndex = startRow;

                while (true)
                {
                    var cellValue = worksheet.Cells[rowIndex, startCol].Text;

                    if (string.IsNullOrEmpty(cellValue))
                    {
                        break;
                    }

                    columnData.Add(cellValue);

                    rowIndex++;
                }
            }

            return columnData;
        }

        public void AddRowsFromCell(string filePath, string sheetName, int startRow, int startCol, List<string[]> rows)
        {
            FileInfo file = new FileInfo(filePath);

            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];

                if (startRow <= 0 || startCol <= 0)
                {
                    throw new ArgumentException("起始行和列必须大于 0");
                }

                foreach (var rowData in rows)
                {
                    int rowIndex = startRow++;

                    for (int colIndex = 0; colIndex < rowData.Length; colIndex++)
                    {
                        worksheet.Cells[rowIndex, startCol + colIndex].Value = rowData[colIndex];
                    }
                }

                package.Save();
            }
        }

        public void AddColumnDataFromCell(string filePath, string sheetName, int startRow, int startCol, List<string> columnData)
        {
            FileInfo file = new FileInfo(filePath);
            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];

                if (startRow <= 0 || startCol <= 0)
                {
                    throw new ArgumentException("起始行和列必须大于 0");
                }
                    
                int rowIndex = startRow;
                foreach (var data in columnData)
                {
                    worksheet.Cells[rowIndex++, startCol].Value = data;
                }

                package.Save();
            }
        }

        public void AddColumnDataFromCellUpward(string filePath, string sheetName, int startRow, int startCol, List<string> columnData)
        {
            FileInfo file = new FileInfo(filePath);
            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];

                if (startRow <= 0 || startCol <= 0)
                {
                    throw new ArgumentException("起始行和列必须大于 0");
                }
                    
                int rowIndex = startRow;

                for (int i = 0; i < columnData.Count; i++)
                {
                    rowIndex--;
                    worksheet.Cells[rowIndex, startCol].Value = columnData[i];
                }

                package.Save();
            }
        }

        public void WriteExcel(string filePath, string sheetName, int row, int col, string value)
        { 
            FileInfo file = new FileInfo(filePath);

            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];
                worksheet.Cells[row, col].Value = value;
                package.Save();
            }
        }

        public void AddRows(string filePath, string sheetName, List<string[]> rows)
        { 
            FileInfo file = new FileInfo(filePath);

            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets[sheetName];
                int lastRow = worksheet.Dimension?.End.Row ?? 0;

                foreach (var rowData in rows)
                {
                    lastRow++; // 新增的行
                    for (int col = 1; col <= rowData.Length; col++)
                    {
                        worksheet.Cells[lastRow, col].Value = rowData[col - 1]; // 批量设置每个单元格的值
                    }
                }

                package.Save();
            }
        }

        public void ClrCollectionStatus()
        {
            for (int i = 0; i < _Channels; i++)
            {
                for (int j = 0; j < _Parameters; j++)
                {
                    collectionStatus[i, j] = false;
                }
            }
        }

        public void ClrRetestDataBuffer()
        {
            ClrCollectionStatus();

            for (int i = 0; i < 32; i++)
            {
                _currentDataCnt[0] = 0;

                for (int j = 0; j < retestData.Length; j++)
                {
                    if (retestData[j] != null)
                    {
                        foreach (List<string> innerList in retestData[j])
                        {
                            innerList.Clear();
                        }
                    }
                }
            }
        }

        public void SaveDataToExcel(string filePath, int chl,string sheetName, string startCell, List<List<string>> data)
        {
            var fileInfo = new FileInfo(filePath);
            bool fileExists = fileInfo.Exists;

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            try
            {
                using (var package = new ExcelPackage(fileInfo))
                {
                    if (!fileExists)
                    {
                        for (int i = 0; i < 32; i++)
                        {
                            var worksheet = package.Workbook.Worksheets.Add($"变送器{i + 1}");
                        }
                    }
                    else
                    {
                        var worksheet = package.Workbook.Worksheets[sheetName] ?? package.Workbook.Worksheets.Add(sheetName);
                        worksheet.Cells.Clear();
                    }

                    var startRow = ExcelCellAddressToRowColumn(startCell).Item1;
                    var startCol = ExcelCellAddressToRowColumn(startCell).Item2;

                    int row = startRow;
                    foreach (var dataRow in data)
                    {
                        int col = startCol;
                        foreach (var val in dataRow)
                        {
                            package.Workbook.Worksheets[sheetName].Cells[row, col].Value = val;
                            col++;
                        }
                        row++;
                    }

                    package.Save();
                }
            }
            catch (Exception ex)
            {
            }
        }

        // 将Excel单元格地址（如"A1"）转换为行列
        private static Tuple<int, int> ExcelCellAddressToRowColumn(string cellAddress)
        {
            var columnLetters = string.Empty;
            var rowNumbers = string.Empty;

            // 获取Excel列字母部分和行号部分
            foreach (var ch in cellAddress)
            {
                if (char.IsLetter(ch))
                {
                    columnLetters += ch;
                }
                else
                {
                    rowNumbers += ch;
                }
            }

            // 转换列字母为列号
            int column = 0;
            foreach (var letter in columnLetters)
            {
                column = column * 26 + (letter - 'A' + 1);
            }

            // 转换行号
            int row = int.Parse(rowNumbers);

            return new Tuple<int, int>(row, column);
        }
    }

    public static class Utils
    {
        public static string BytesArrayToHexString(byte[] bytes, string prefix = "0x", string suffix = "")
        { 
            StringBuilder hexString = new StringBuilder(bytes.Length * (prefix.Length + 2 + suffix.Length));

            foreach (byte b in bytes )
            {
                hexString.AppendFormat("{0}{1:X2}{2}", prefix, b, suffix);
            }

            // Delete the last extra space
            return hexString.ToString().TrimEnd();
        }

        static public string HexToString(byte[] data)
        {
            string resultStr = null;

            for (byte i = 0; i < data.Length; i++)
            {
                if (i == (data.Length - 1))
                {
                    resultStr += data[i].ToString("X2");
                }
                else
                {
                    resultStr += data[i].ToString("X2") + " ";
                }
            }

            return resultStr;
        }

        public static byte GetHARTCRC(byte[] pData, byte size)
        {
            byte ucCRC = 0;
            byte i = 0;

            for (i = 0; i < size; i++)
            {
                ucCRC ^= pData[i];
            }

            return ucCRC;
        }

        public static byte[] GetHARTDataFrame(byte[] data)
        {
            byte frameStart = 0;

            for (byte i = 0; i < data.Length; i++)
            {
                if ((data[i] == 0x06) || (data[i] == 0x86))
                {
                    frameStart = i;
                    break;
                }
            }

            ArraySegment<byte> segment = new ArraySegment<byte>(data, frameStart, data.Length - frameStart);
                
            return segment.ToArray();
        }

        public static byte CountTrailingZeros(uint number)
        {
            byte count = 0;

            if (number == 0)
            {
                return 0x00;
            }

            while ((number & 1) == 0)
            {
                number >>= 1;
                count++;
            }
            return count;
        }

        public static string GetUnit(byte id)
        {
            string unit = null;

            switch (id)
            {
                case 6:
                    unit = "psi";
                    break;
                case 7:
                    unit = "bar";
                    break;
                case 11:
                    unit = "Pa";
                    break;
                case 12:
                    unit = "kPa";
                    break;
                case 13:
                    unit = "Torr";
                    break;
                case 237:
                    unit = "MPa";
                    break;
                case 239:
                    unit = "mmH2O";
                    break;
                default:
                    unit = "kPa";
                    break;
            }

            return unit;
        }
    }
}
